/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.campaign.dao;

import com.aptech.campaign.entities.Product;
import com.aptech.campaign.util.ConfigureDB;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;

/**
 *
 * @author Tai Nguyen
 */
public class ProductDAO {

    private String lasterror;
    String sql = null;
    Connection con = null;
    ConfigureDB db = null;
    PreparedStatement pst = null;
    CachedRowSet crs = null;

    public ProductDAO() {
        db = new ConfigureDB();
    }

    /**
     * add a new product
     * @param Product product
     * @return the boolean value
     */
    public boolean createProduct(Product product) {
        con = db.getConnection();
        sql = "insert into [Product] values (?, ?, ?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, product.getProductName());
            pst.setFloat(2, product.getPrice());
            pst.setString(3, product.getInfo());
            pst.setBoolean(4, product.isStatus());
            if (pst.executeUpdate() > 0) {
                setLasterror("Add Product successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * edit information of product
     * @param int productID, String productName, float price, String info
     * @return the boolean value
     */
    public boolean updateProduct(int productID, String productName, float price, String info) {
        con = db.getConnection();
        sql = "update [Product] set productName = ?, Price = ?, Info = ? where ProductID = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, productName);
            pst.setFloat(2, price);
            pst.setString(3, info);
            pst.setInt(4, productID);
            if (pst.executeUpdate() > 0) {
                setLasterror("Update product Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * delete a product
     * @param int productID
     * @return the boolean value
     */
    public boolean deleteProduct(int productID) {
        con = db.getConnection();
        sql = "update [Product] set Status = ? where ProductID = ?";

        try {
            pst = con.prepareStatement(sql);
            pst.setBoolean(1, false);
            pst.setInt(2, productID);
            if (pst.executeUpdate() > 0) {
                setLasterror("Delete Product Successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * get array of products in database
     * Param
     * @return ArrayList<Product>
     */
    public ArrayList<Product> readByAllProduct() {
        ArrayList<Product> arr = new ArrayList<Product>();
        con = db.getConnection();
        sql = "select * from [Product] where Status = ?";
        try {
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.setBoolean(1, true);
            crs.execute(con);
            while (crs.next()) {
                Product pro = new Product();
                pro.setProductID(crs.getInt(1));
                pro.setProductName(crs.getString(2));
                pro.setPrice(crs.getFloat(3));
                pro.setInfo(crs.getString(4));
                pro.setStatus(crs.getBoolean(5));
                arr.add(pro);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     * @return the lasterror
     */
    public String getLasterror() {
        return lasterror;
    }

    /**
     * @param lasterror the lasterror to set
     */
    public void setLasterror(String lasterror) {
        this.lasterror = lasterror;
    }
}
